package server;

import java.sql.*;
import java.util.Vector;
import beans.Player_Bean;
import beans.Team_Bean;
import beans.User_Bean;

/**********************************************************************
*
* Filename: Database_Service.java
*
* File Description:
* Service that will handles all database transactions
*
* Author:
* @author Tim Schmalz
* Date Created:
*  Oct 9, 2009
*
* Modified:
* @author Brandon Fulco
* Date Modified:
*  Oct 25, 2009
**********************************************************************/
public class Database_Service
{
	//Database Constants
	private final String DATABASE_NAME = "FBT52009";
	private final String USER_NAME = "broomball";
	private final String PASSWORD = "broomball";

	//Constants for the player table
	private final int PLAYER_ID = 1;
	private final int PLAYER_NAME = 2;
	private final int PLAYER_ISO = 3;

	//Constants for the Team Table
	private final int TEAM_ID = 1;
	private final int LEAGUE_ID = 2;
        private final int TEAM_NAME = 2;

       	private Connection connection = null;

        /**
         * Establishes a connection to the database
         *
         */
	public void connectToDatabase()
	{
		try
		{
			//Register the JDBC driver for MySQL.
			Class.forName("com.mysql.jdbc.Driver").newInstance();

			//Declare the URL of the database server
			String URL = "jdbc:mysql://orion.csl.mtu.edu:3306/" + DATABASE_NAME;

			//Get a connection to the database server
			connection = DriverManager.getConnection(URL,USER_NAME,PASSWORD);
		}
		catch(Exception e)
		{
			System.out.println(e);
		}
	}

        /**
         * Gets all of the players from the database
         *
         * @return A vector of Player_Beans
         */
	public Vector<Player_Bean> queryPlayers()
	{
		Vector<Player_Bean> playerVector = new Vector<Player_Bean>();

		try
		{
			if(connection == null)
			{
				throw new Exception("Connection was not present before queryPlayers() was called");
			}

			Statement statement;
			ResultSet resultSet;

			//Get a Statement object
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

			resultSet = statement.executeQuery("SELECT * FROM PLAYERS;");

			while(resultSet.next())
			{
				Player_Bean player = new Player_Bean();
				player.setPlayerId(Integer.parseInt(resultSet.getString(PLAYER_ID)));
				player.setPlayerName(resultSet.getString(PLAYER_NAME));
				player.setPlayerIso(resultSet.getString(PLAYER_ISO));
				playerVector.add(player);
			}
		}
		catch(Exception e)
		{
			System.err.println(e.toString());
		}
		return playerVector;
	}

                /**
         * Gets all of the players from the database
         *
         * @return A vector of Player_Beans
         */
	public Vector<Team_Bean> queryTeams(int leagueId)
	{
		Vector<Team_Bean> teamVector = new Vector<Team_Bean>();

		try
		{
			if(connection == null)
			{
				throw new Exception("Connection was not present before queryTeam() was called");
			}

			Statement statement;
			ResultSet resultSet;

			//Get a Statement object
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

			resultSet = statement.executeQuery("SELECT TEAM_ID, TEAM_NAME FROM TEAMS " +
                                                            "WHERE LEAGUE_ID=" + leagueId + ";");

			while(resultSet.next())
			{
				Team_Bean team = new Team_Bean(
                                        Integer.parseInt(resultSet.getString(TEAM_ID)),
                                        resultSet.getString(TEAM_NAME));
				teamVector.add(team);
                                System.out.println(team.getTeamId());
			}
		}
		catch(Exception e)
		{
			System.err.println(e.toString());
		}
		return teamVector;
	}

	/**
	 * Gets the size of the league from the database
         *
         * @param leagueId - League's ID
	 * @return The size of the given league's ID
	 */
	public int getSizeOfLeague(int leagueId)
	{
		try
		{
			if(connection == null)
			{
				throw new Exception("Connection was not present before getSizeOfLeague() was called");
			}

			Statement statement;
			ResultSet resultSet;

			//Get a Statement object
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

			resultSet = statement.executeQuery("SELECT count(*)" +
							   " FROM TEAMS " +
							   " WHERE LEAGUE_ID = '" + leagueId + "';");
                        if (resultSet.next())
                            return Integer.parseInt(resultSet.getString("count(*)"));
		}
		catch(Exception e)
		{
			System.err.println(e.toString());
		}

		return -1;
	}

	/**
	 * Checks it the user is in the database
         *
	 * @param user - A User_Bean containing the users login information (Password is encrypted with MD5)
	 * @return A User_Bean containing a valid users information, if user is not valid returns null
	 */
	public User_Bean login(User_Bean user)
	{
		String query = "NO SQL PRESENT";
		try
		{
			if(connection == null)
			{
				throw new Exception("Connection was not present before login() was called");
			}

			Statement statement;
			ResultSet resultSet;

			//Get a Statement object
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			query = 
                        "SELECT TEAM_ID, LEAGUE_ID" +
                                " FROM FBT52009.TEAMS WHERE TEAMS.TEAM_NAME='" + user.getUserName() + "'" +
                                    " AND TEAMS.TEAM_PWD='" + user.getPassword() + "'" +
                                    " AND TEAMS.LEAGUE_ID IN" +
                                        " (SELECT LEAGUE_ID FROM FBT52009.LEAGUES WHERE LEAGUES.LEAGUE_NAME='" + user.getLeagueName() + "');";
                        System.out.println(query);
			resultSet = statement.executeQuery(query);
			if(resultSet.next())
			{
				user.setTeamId(Integer.parseInt(resultSet.getString(TEAM_ID)));
				user.setLeagueId(Integer.parseInt(resultSet.getString(LEAGUE_ID)));
			}
			else
			{	
				user = null;
			}
		}
		catch(SQLException sqle)
		{
			System.err.println("Error in the following SQL statement/n" + query);
			System.err.println(sqle);
			user = null;
		}
		catch(Exception e)
		{
			System.err.println("Connection not present during login() call");
			user = null;
		}
		return user;
	}

        /**
         * Closes the connection with the database
         *
         */
	public void closeConnection()
	{
		//Close the database connection
		try
		{
			connection.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

        /**
         * Add player to the teams roster in player database
         *
         * @param playerID - Player's id
         * @param teamID - Team's id
         * @param pos - Added to ice or bench
         * @return query success
         */
        public boolean addPlayerToRoster(int playerID, int teamID, int pos)
        {
            String query = "NO SQL PRESENT";
		try
		{
			if(connection == null)
			{
				throw new Exception("Connection was not present before addPlayerToRoster() was called");
			}

			Statement statement;

			//Get a Statement object
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			query = "INSERT INTO ROSTER (TEAM_ID, PLAYER_ID, POSITION)" +
			"VALUES ('" + teamID + "', '" + playerID + "', '" + pos + "');";
			statement.executeUpdate(query);
                        return true;
		}
		catch(SQLException sqle)
		{
			System.err.println("Error in the following SQL statement/n" + query);
			System.err.println(sqle);
		}
		catch(Exception e)
		{
			System.err.println("Connection not present during login() call");
		}
		return false;
        }

        /**
         * Sets the draft to done when teams have completed the draft
         *
         * @param leagueId - League to have draft closed
         * @return success of query
         */
        public boolean setDraftToDone(int leagueId)
        {
            String query = "NO SQL PRESENT";
		try
		{
			if(connection == null)
			{
				throw new Exception("Connection was not present before addsetDraftToDone() was called");
			}

			Statement statement;

			//Get a Statement object
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			query = "UPDATE LEAGUES " +
                                "SET DRAFT_DONE = 1 " +
                                "WHERE LEAGUE_ID = " + leagueId +";";
			statement.executeUpdate(query);
                        return true;
		}
		catch(SQLException sqle)
		{
			System.err.println("Error in the following SQL statement/n" + query);
			System.err.println(sqle);
		}
		catch(Exception e)
		{
			System.err.println("Connection not present during setDraftToDone() call");
		}
		return false;
        }

        /**
         * Gets the of the team's current roster
         *
         * @param teamId - Id of team to check
         * @return Size of team
         */
        public int getSizeRoster(int teamId)
	{
		try
		{
			if(connection == null)
			{
				throw new Exception("Connection was not present before getSizeRoster() was called");
			}

			Statement statement;
			ResultSet resultSet;

			//Get a Statement object
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

			resultSet = statement.executeQuery("SELECT count(*)" +
							   " FROM ROSTER " +
							   " WHERE TEAM_ID = '" + teamId + "';");
                        if (resultSet.next())
                            return Integer.parseInt(resultSet.getString("count(*)"));
		}
		catch(Exception e)
		{
			System.err.println(e.toString());
		}

		return -1;
	}
}